package com.sun.showcase.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sun.showcase.utils.Format;


public class ReadExcel {
	private int headerCount=1;
	private String filePath="";
	private String extension="xls";
	
	public ReadExcel() {
		super();
	}

	public ReadExcel(String filePath){
		this.filePath=filePath;
	}
	
	private List<Map<String,String>> arrayToListMap(String[][] arr)
	{
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		if (arr == null||arr.length==0){
			return list;
		}
		//Map rowData = new HashMap();
		int columnCount=arr[0].length;
		for(int i=headerCount;i<arr.length;i++)
		{
			Map<String,String> rowData = new HashMap<String,String>(columnCount);
			for(int j=0;j<columnCount;j++)
			{
				rowData.put(arr[0][j], arr[i][j]);
			}
			list.add(rowData);
		}
		return list;
	}

	/**
	 * 对外提供读取excel 的方法
	 * */
	public List<Map<String,String>> readExcel() throws IOException {
		
		File file=new File(filePath);
		String[][] result = null;
		if ("xls".equals(extension)) {
			result = read2003Excel(file);
		} else if ("xlsx".equals(extension)) {
			result = read2007Excel(file);
		} else {
			throw new IOException("不支持的文件类型");
		}
		return arrayToListMap(result);
	}
	/**
	 * 对外提供读取excel 的方法
	 * */
	public List<Map<String,String>> readExcelStream(InputStream stream) throws IOException {
		
		String[][] result = null;
		if ("xls".equals(extension)) {
			result = read2003ExcelStream(stream);
		} else if ("xlsx".equals(extension)) {
			result = read2007ExcelStream(stream);
		} else {
			throw new IOException("不支持的文件类型");
		}
		return arrayToListMap(result);
	}
	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	private String[][] read2003Excel(File file) throws IOException {
		// 创建新的 excel 工作博
		HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
		return this.read2003Excel(hwb);
	}
	private String[][] read2003ExcelStream(InputStream stream) throws IOException{
		HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(stream));
		return this.read2003Excel(hwb);
	}
	private String[][] read2003Excel(HSSFWorkbook hwb){
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		HSSFCell cell = null;
		for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
			if(i==1){
				break;
			}
			HSSFSheet sheet = hwb.getSheetAt(i);
			//暂时不读第一个sheet外的内容
			
			// 循环每行 包括标题 导航.
			for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				HSSFRow row = sheet.getRow(rowIndex);// 得到每一行的数据
				if (row == null) {
					continue;
				}
				// row.getLastCellNum() 是获取最后一个不为空的列是第几个
				int tempRowSize = row.getLastCellNum();
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell == null) {
						continue;
						
					}
					// 注意：一定要设成这个，否则 有可能 会出现乱码
					// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							Date date = cell.getDateCellValue();
							if (date != null) {
								value = new SimpleDateFormat(Format.DATE_PATTERN)
										.format(date);
							} else {
								value = "";
							}
						} else {
//							value = new DecimalFormat("0").format(cell
//									.getNumericCellValue()); Format.formatNumber(numTxt,"#.######"); 
							value = Format.formatNumber(cell.getNumericCellValue(),"#.######");
						}
						break;
					case XSSFCell.CELL_TYPE_FORMULA:
						// 导入时如果为公式生成的数据则无值
						try{
							value=cell.getStringCellValue();
							if (!"".equals(cell.getStringCellValue())) {
								value = cell.getStringCellValue();
							} else {
								value = Format.formatNumber(cell.getNumericCellValue(),"#.######") + "";
							}
						}
						catch(Exception e){
							value = Format.formatNumber(cell.getNumericCellValue(),"#.######") + "";
						}
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = (cell.getBooleanCellValue()? "Y" : "N");
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						value = "";
						break;
					case XSSFCell.CELL_TYPE_ERROR:
						value = "";
						break;
					default:
						value = cell.toString();
					}
//					if (columnIndex == 0 && "".equals(value.trim())) {
//						break;
//					}
					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					result.add(values);
				}
			}
		}
		String[][] returnArray = new String[result.size()][rowSize];
		for (int m = 0; m < returnArray.length; m++) {
			returnArray[m] = (String[]) result.get(m);
		}
		return returnArray;
	}
	private String[][] read2007Excel(File file) throws IOException {
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
		return this.read2007Excel(xwb);
	}
	private String[][] read2007ExcelStream(InputStream stream) throws IOException {
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(stream);
		return this.read2007Excel(xwb);
	}
	/**
	 * 读取Office 2007 excel
	 * */

	private String[][] read2007Excel(XSSFWorkbook xwb) throws IOException {

		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		XSSFCell cell = null;
		
		for (int sheetIndex = 0; sheetIndex < xwb.getNumberOfSheets(); sheetIndex++) {
			if(sheetIndex==1){
				break;
			}
			XSSFSheet sheet = xwb.getSheetAt(sheetIndex);
			for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
				XSSFRow row = sheet.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum();
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);// 得到的每行的每个具体的值
					if (cell != null) {
						switch (cell.getCellType()) {
						case XSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;
						//数值类型转化为字符串
						case XSSFCell.CELL_TYPE_NUMERIC:
//							SimpleDateFormat sdf = new SimpleDateFormat(
//									Format.DATE_PATTERN);// 格式化日期字符串
//							/*
//							 * if
//							 * ("@".equals(cell.getCellStyle().getDataFormatString
//							 * ())) { value = new
//							 * DecimalFormat("0").format(cell.
//							 * getNumericCellValue()); } else
//							 */if ("General".equals(cell.getCellStyle()
//									.getDataFormatString())) {
////								value = new DecimalFormat("0").format(cell
////										.getNumericCellValue());
//								 value = Format.formatNumber(cell.getNumericCellValue(),"#.######");
//							} else {
//								value = sdf
//										.format(HSSFDateUtil.getJavaDate(cell
//												.getNumericCellValue()));
//							}
							 
							 if (HSSFDateUtil.isCellDateFormatted(cell)) {
									Date date = cell.getDateCellValue();
									if (date != null) {
										value = new SimpleDateFormat(Format.DATE_PATTERN)
												.format(date);
									} else {
										value = "";
									}
								} else {
//									value = new DecimalFormat("0").format(cell
//											.getNumericCellValue()); Format.formatNumber(numTxt,"#.######"); 
									value = Format.formatNumber(cell.getNumericCellValue(),"#.######");
								}
							 
							break;
						case XSSFCell.CELL_TYPE_FORMULA:
							// 导入时如果为公式生成的数据则无值
							try{
								value=cell.getStringCellValue();
								if (!"".equals(cell.getStringCellValue())) {
									value = cell.getStringCellValue();
								} else {
									value = Format.formatNumber(cell.getNumericCellValue(),"#.######") + "";
								}
							}
							catch(Exception e){
								value = Format.formatNumber(cell.getNumericCellValue(),"#.######") + "";
							}
//							if (!cell.getStringCellValue().equals("")) {
//								value = cell.getStringCellValue();
//							} else {
//								value = cell.getNumericCellValue() + "";
//							}
							break;
						case XSSFCell.CELL_TYPE_BLANK:
							break;
						case XSSFCell.CELL_TYPE_ERROR:
							value = "";
							break;
						case XSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue()? "Y" : "N");
							break;
						default:
							value = "";
							break;
						}
					}
//					if (columnIndex == 0 && value.trim().equals("")) {
//						break;
//					}
					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					result.add(values);
				}
			}
		}
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = (String[]) result.get(i);
		}
		return returnArray;
	}

	/**
	 * 去掉字符串右边的空格
	 * 
	 * @param value
	 *            要处理的字符串
	 * @return 处理后的字符串
	 */
	public String rightTrim(String value) {
		if (value == null) {
			return "";
		}
		int length = value.length();
		for (int i = length - 1; i >= 0; i--) {
			if (value.charAt(i) != 0x20) {
				break;
			}
			length--;
		}
		return value.substring(0, length);
	}
	public static String getExtension(String fileName) {
		String extension = fileName.lastIndexOf('.') == -1 ? "" : fileName
				.substring(fileName.lastIndexOf('.') + 1);
		return extension;
	}
	public int getHeaderCount() {
		return headerCount;
	}

	public void setHeaderCount(int headerCount) {
		this.headerCount = headerCount;
	}

	public String getFilePath() {
		return filePath;
	}

	public void setFilePath(String filePath) {
		this.filePath = filePath;
	}

	public String getExtension() {
		return extension;
	}

	public void setExtension(String extension) {
		this.extension = extension;
	}

}
